package com.fruits.dao.impl;

import com.fruits.domain.Fruit;
import com.fruits.dao.FruitDao;
import com.fruits.util.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

public class FruitDaoImpl implements FruitDao {
    private QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

    @Override
    public List<Fruit> findHot() {
        String sql = "  SELECT t2.fid, t2.fname, t2.spec,t2.up, t2.t1 origin, t2.t2 detail  FROM hotfruits t1, fruits t2 WHERE t1.fid = t2.fid";
        List<Fruit> fruitList = null;
        try {
            fruitList = queryRunner.query(sql, new BeanListHandler<Fruit>(Fruit.class));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return fruitList;
    }

    @Override
    public Fruit findByFid(int fid) {
        String sql = "SELECT * FROM fruits WHERE fid=?";
        Object[] params = {fid};
        try {
            Fruit fruit = queryRunner.query(sql, new BeanHandler<Fruit>(Fruit.class), params);
            return fruit;

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }


    }

    @Override
    public List<Fruit> findByStr(String keyword) {
        String sql = "SELECT * FROM fruits WHERE fname LIKE ?";
        Object[] params = {"%" + keyword + "%"};
        List<Fruit> fruit = null;
        try {
            fruit = queryRunner.query(sql, new BeanListHandler<Fruit>(Fruit.class), params);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return fruit;
    }

    @Override
    public List<Fruit> findAll() {
        String sql = "SELECT * FROM fruits";
        List<Fruit> fruit = null;
        try {
            fruit = queryRunner.query(sql, new BeanListHandler<Fruit>(Fruit.class));
            return fruit;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int add(Fruit fruit) {
        String sql = "INSERT INTO fruits VALUES(?,?,?,?,?,?,?)";
        Object[] params = {fruit.getFid(), fruit.getFname(), fruit.getSpec(), fruit.getUp(), fruit.getOrigin(), fruit.getDetail(), fruit.getInum()};
        try {
            int add = queryRunner.update(sql, params);
            return add;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int update(Fruit fruit) {
        String sql = "UPDATE fruits SET fid=?,fname=?,spec=?,up=?,t1=?,t2=?,inum=? WHERE fid=?";
        Object[] params = {fruit.getFid(), fruit.getFname(), fruit.getSpec(), fruit.getUp(), fruit.getOrigin(), fruit.getDetail(), fruit.getInum(),fruit.getFid()};
        try {
            int update = queryRunner.update(sql, params);
            return update;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int delete(int fid) {
        String sql = "DELETE FROM fruits WHERE fid=?";
        Object[] params = {fid};
        try {
            int del = queryRunner.update(sql, params);
            return del;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Fruit> searchByOrigin() {
        String sql = "SELECT  t2.fid, t2.fname, t2.spec,t2.up, t2.t1 origin, t2.t2 detail FROM fruits t2 WHERE t1 NOT LIKE '%中国%' AND t1 NOT LIKE '%国产%'";
        try {
            List<Fruit> fruits = queryRunner.query(sql, new BeanListHandler<Fruit>(Fruit.class));
            return fruits;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public List<Fruit> searchByChinaOrigin() {
        String sql = "SELECT  t2.fid, t2.fname, t2.spec,t2.up, t2.t1 origin, t2.t2 detail FROM fruits t2 WHERE t1 LIKE '%中国%' OR t1 LIKE '%国产%'";
        try {
            List<Fruit> fruits = queryRunner.query(sql, new BeanListHandler<Fruit>(Fruit.class));
            return fruits;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}



